/**
 * 
 */
package com.jimet.server;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Enumeration;
import java.util.Properties;

import java.sql.Statement;

import com.jimet.authorization.JimetAuthorization;
import com.jimet.repository.JimetRepositoryConnection;

public class JimetSupervisorDB extends JimetRepositoryConnection {

	/**
	 * @throws Exception
	 */
	public JimetSupervisorDB() throws Exception {
		super();
	}
	
	public boolean insertAction(int userID, String remoteHost, int actionID, String actionParams) throws Exception
	{
		
		PreparedStatement stmt;
		String queryStr = "INSERT INTO USR_ACTION_HISTORY " +
				" (USR_ID, REMOTE_HOST, ACTION_ID, ACTION_PARAMS) " +
				" VALUES(?, ?, ?, ?) ";
		
		stmt = connection.prepareStatement(queryStr);
		stmt.setInt(1,userID);
		stmt.setString(2,remoteHost);
		stmt.setInt(3,actionID);
		stmt.setString(4,actionParams);

		try
		{
			stmt.executeUpdate();
		}
		catch(Exception ex)
		{
			throw ex;
		}
		return true;

	}
	
	public int getActionID(String shortCode) throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;
		String queryStr = 	"SELECT ACT_ID from USR_ACTION " +
							" WHERE ACT_CODE = ?";
		
		stmt = connection.prepareStatement(queryStr);
		stmt.setString(1,shortCode);
		rs = stmt.executeQuery();

		if(rs.next())
		{
			int actionID = rs.getInt(1);
			return actionID;
		}
		else
		{
			throw new Exception("The specified action short code is not valid. : " + shortCode);
		}
	}
	
	public ResultSet getUserAuthorizationSet(int userID) throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;
		String queryStr = "SELECT USR_RIGHT.RIGHT_ID, USR_RIGHT.RIGHT_CODE, " +
				" USR_RIGHT.RIGHT_DESC " +
				" FROM USR_RIGHT AS USR_RIGHT, USR_RIGHT_OWNER AS USR_RIGHT_OWNER " +
				" WHERE USR_RIGHT.RIGHT_ID = USR_RIGHT_OWNER.RIGHT_ID " +
				" AND USR_RIGHT_OWNER.RIGHT_USR_ID = ? ";
		stmt = connection.prepareStatement(queryStr);
		stmt.setInt(1,userID);
		rs = stmt.executeQuery();
		return rs;
	}

	/**gives the first level parent group of the given user
	 * @param userID - to get parents
	 * @return
	 * @throws Exception
	 */
	public ResultSet getUserParentsSet(int userID) throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;
		String queryStr = "SELECT USR_GRP_ID" +
				" FROM .USR_GRP_RELATIONS" +
				" WHERE USR_ID =?";
		stmt = connection.prepareStatement(queryStr);
		stmt.setInt(1,userID);
		rs = stmt.executeQuery();
		return rs;
	}
	
	
	/**  Gets the authorizations of the group as a resultset
	 * @param grpID the group id
	 * @return the resultset object containig the authorizations of the group
	 * @throws Exception
	 */
	public ResultSet getGroupAuthorizationSet(int grpID) throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;
		String queryStr = "SELECT GRP_RIGHT_OWNER.*,USR_RIGHT.RIGHT_CODE" +
				" FROM GRP_RIGHT_OWNER AS GRP_RIGHT_OWNER , USR_RIGHT AS USR_RIGHT " +
				" WHERE GRP_RIGHT_OWNER.RIGHT_ID = USR_RIGHT.RIGHT_ID" +
				" AND GRP_RIGHT_OWNER.RIGHT_GROUP_ID = ?";
		stmt = connection.prepareStatement(queryStr);
		stmt.setInt(1,grpID);
		rs = stmt.executeQuery();
		
		return rs;
	}
	
	/**Gives the first level parents of the given group
	 * @param grpID - to get All parents
	 * @return
	 * @throws Exception
	 */
	public ResultSet getGroupParentsSet(int grpID) throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;
		String queryStr = "SELECT GRP_ID " +
				"FROM GRP_RELATIONS " +
				"WHERE GRP_CHILD_ID = ?";
		stmt = connection.prepareStatement(queryStr);
		stmt.setInt(1,grpID);
		rs = stmt.executeQuery();
		
		return rs;
	}
	
	public boolean isGroupAuthorized(String shortCode, String groupId) throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;
		String queryStr = "select GRP_RIGHT_OWNER.*, USR_RIGHT.RIGHT_CODE" +
				" from GRP_RIGHT_OWNER AS GRP_RIGHT_OWNER , USR_RIGHT AS USR_RIGHT" +
				" where GRP_RIGHT_OWNER.RIGHT_ID = USR_RIGHT.RIGHT_ID" +
				" and USR_RIGHT.RIGHT_CODE = ?" +
				" and GRP_RIGHT_OWNER.RIGHT_GROUP_ID = ?";

		stmt = connection.prepareStatement(queryStr);
		stmt.setString(1,shortCode);
		stmt.setInt(2,Integer.parseInt(groupId));
		rs = stmt.executeQuery();
		
		if (rs.next()) 
		{
			return true;
		}
		else
		{ 
			queryStr = "select *" +
					" from GRP_RELATIONS " +
					"where GRP_CHILD_ID = ?";
			
			stmt = connection.prepareStatement(queryStr);
			stmt.setString(1,groupId);
			rs = stmt.executeQuery();
			
			while(rs.next())
			{
				if(isGroupAuthorized(shortCode, rs.getString(1)))
					return true;
			}
			return false;
		}
	}

	public ResultSet getPropsByName(String tblPrefix, String propName) throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;
		String queryStr = "SELECT * " +
				"from "+tblPrefix+"_PROP_IDENT " +
				"WHERE PROP_NAME = ?" +
				" ORDER BY PROP_NAME DESC";
		stmt = connection.prepareStatement(queryStr);
		stmt.setString(1,propName);
		rs = stmt.executeQuery();
		return rs;
	}



	public boolean addAction(String shortCode, String description) throws Exception
	{
	
		PreparedStatement stmt;
		String queryStr = "INSERT INTO USR_ACTION " +
				" (ACT_CODE,ACT_DESC) " +
				" VALUES(?,?) ";
		stmt = connection.prepareStatement(queryStr);
		stmt.setString(1,shortCode);
		stmt.setString(2,description);
	
		try
		{
			stmt.executeUpdate();
		}
		catch(Exception ex)
		{
			return false;
		}
		return true;
	}


	public boolean addAuth(String shortCode, String description, String authType) throws Exception
	{

		PreparedStatement stmt;
		String queryStr = "INSERT INTO USR_RIGHT " +
				" (RIGHT_CODE , RIGHT_DESC, RIGHT_TYPE) " +
				" VALUES(?,?,?) ";
		stmt = connection.prepareStatement(queryStr);
		stmt.setString(1,shortCode);
		stmt.setString(2,description);
		stmt.setString(3,authType);

		try
		{
			stmt.executeUpdate();
		}
		catch(Exception ex)
		{
			System.out.println(ex.getMessage());
			return false;
		}
		return true;
	}



	public boolean addFolder(String foldername, String description, int parentID) throws Exception
	{
		PreparedStatement stmt;
		String queryStr = "INSERT INTO FLD_IDENT " +
				" (FLD_NAME,FLD_DESC,FLD_PARENT_ID) " +
				" VALUES(?,?,?) ";
		stmt = connection.prepareStatement(queryStr);
		stmt.setString(1,foldername);
		stmt.setString(2,description);
		stmt.setInt(3,parentID);
		try
		{
			stmt.executeUpdate();
		}
		catch(Exception ex)
		{
			return false;
		}
		return true;
	}


	public String addForm(String formName, String actionURL, String attributes) throws Exception
	{
	
		PreparedStatement stmt;
		String queryStr = "INSERT INTO FRM_IDENT " +
				" (FRM_NAME,FRM_ACTION_URL,FRM_ATTRIBUTES) " +
				" VALUES(?,?,?) ";
		stmt = connection.prepareStatement(queryStr);
		stmt.setString(1,formName);
		stmt.setString(2,actionURL);
		stmt.setString(3,attributes);
	
		try
		{
			stmt.executeUpdate();
		}
		catch(Exception ex)
		{
			return "";
		}

		queryStr = 	" SELECT MAX(FRM_ID) " +
					" FROM FRM_IDENT " ;
		stmt = connection.prepareStatement(queryStr);
		ResultSet rs = stmt.executeQuery();
		if(rs.next())
			return rs.getString(1);
		else
			return "";
	}


	public int addGroup(String groupname, String description) throws Exception
	{
		PreparedStatement stmt;
		ResultSet rs;
		int id = -1;
		String queryStr = "INSERT INTO GRP_IDENT " +
				" (GRP_NAME,GRP_DESC) " +
				" VALUES(?,?) ";
		stmt = connection.prepareStatement(queryStr);
		stmt.setString(1,groupname);
		stmt.setString(2,description);
	
		try
		{
			stmt.executeUpdate();
			rs = connection.prepareStatement("SELECT SYSIBM.IDENTITY_VAL_LOCAL() FROM GRP_IDENT").executeQuery();
			rs.next();
			id = rs.getInt(1);
		}
		catch(Exception ex)
		{
			return id;
		}
		return id;
	}


	public boolean addGroupAuthorization(int groupId, String authId) throws Exception
	{
		PreparedStatement stmt;
		String queryStr = "INSERT INTO GRP_RIGHT_OWNER " +
				"(RIGHT_GROUP_ID,RIGHT_ID) " +
				"VALUES (?,?)";
		stmt = connection.prepareStatement(queryStr);
		stmt.setInt(1, groupId);
		stmt.setString(2, authId);
	
		try
		{
			stmt.executeUpdate();
		}
		catch(Exception ex)
		{
			return false;
		}
		return true;		
		
	}
	
	
	public boolean removeUserRight(int userId,int authId) throws Exception
	{
		PreparedStatement stmt;
		String queryStr = "DELETE FROM USR_RIGHT_OWNER WHERE (RIGHT_USR_ID = ?)" +
							"AND RIGHT_ID = ?";
		stmt = connection.prepareStatement(queryStr);
		stmt.setInt(1,userId);
		stmt.setInt(2,authId);
	
			try
			{
				stmt.executeUpdate();
			}
			catch(Exception ex)
			{
				return false;
			}
			return true;
	}

	public boolean removeUserRight(int userId,String authName) throws Exception
	{
		JimetAuthorization jauth = new JimetAuthorization();
		jauth.loadByName(authName);
		
		PreparedStatement stmt;
		String queryStr = "DELETE FROM USR_RIGHT_OWNER WHERE (RIGHT_USR_ID = ?)" +
							"AND RIGHT_ID = ?";
		stmt = connection.prepareStatement(queryStr);
		stmt.setInt(1,userId);
		stmt.setInt(2,jauth.getId());
	
			try
			{
				stmt.executeUpdate();
			}
			catch(Exception ex)
			{
				return false;
			}
			return true;
	}

	public boolean giveUserRight(int userId,int authId) throws Exception
	{
		PreparedStatement stmt;
		String queryStr = "INSERT INTO USR_RIGHT_OWNER " +
				"(RIGHT_USR_ID,RIGHT_ID) " +
				"VALUES (?,?)";
		stmt = connection.prepareStatement(queryStr);
		stmt.setInt(1, userId);
		stmt.setInt(2, authId);
	
		try
		{
			stmt.executeUpdate();
		}
		catch(Exception ex)
		{
			return false;
		}
		return true;
	}

	public boolean giveUserRightBD(int userId,int authId) throws Exception
	{
		PreparedStatement stmt;
		String queryStr = "INSERT INTO BDS.USR_RIGHT_OWNER " +
				"(RIGHT_USR_ID,RIGHT_ID) " +
				"VALUES (?,?)";
		stmt = connection.prepareStatement(queryStr);
		stmt.setInt(1, userId);
		stmt.setInt(2, authId);
	
		try
		{
			stmt.executeUpdate();
		}
		catch(Exception ex)
		{
			return false;
		}
		return true;
	}
	
	public boolean giveUserRight(int userId,String authName) throws Exception
	{
		JimetAuthorization jauth = new JimetAuthorization();
		jauth.loadByName(authName);
		
		PreparedStatement stmt;
		String queryStr = "INSERT INTO USR_RIGHT_OWNER " +
				"(RIGHT_USR_ID,RIGHT_ID) " +
				"VALUES (?,?)";
		stmt = connection.prepareStatement(queryStr);
		stmt.setInt(1, userId);
		stmt.setInt(2, jauth.getId());
	
		try
		{
			stmt.executeUpdate();
		}
		catch(Exception ex)
		{
			return false;
		}
		return true;
	}

	public boolean addProperty(String prefix, String name, String type) throws Exception
	{
		PreparedStatement stmt;
		String queryStr = "INSERT INTO " + prefix + "_PROP_IDENT " +
				" (PROP_NAME,PROP_TYPE) " +
				" VALUES(?,?) ";
		stmt = connection.prepareStatement(queryStr);
		stmt.setString(1,name);
		stmt.setString(2,type);
	
			try
			{
				stmt.executeUpdate();
			}
			catch(Exception ex)
			{
				return false;
			}
			return true;
	}


	public boolean addPropertyToObj(String prefix, int propId, String value, int objId) throws Exception
	{
		PreparedStatement stmt;
		String queryStr = "INSERT INTO " + prefix + "_PROP " +
				" ("+ prefix +"_ID,PROP_ID,PROP_VALUE) " +
				" VALUES(?,?,?) ";
		stmt = connection.prepareStatement(queryStr);
		stmt.setInt(1,objId);
		stmt.setInt(2,propId);
		stmt.setString(3,value);
	
			try
			{
				stmt.executeUpdate();
			}
			catch(Exception ex)
			{
				return false;
			}
			return true;
	}


	public boolean addUser(String username, String passwordMD5) throws Exception
	{
		
		PreparedStatement stmt;
		ResultSet rs;
		String queryStr = "INSERT INTO USR_IDENT " +
				" (USR_USERNAME,USR_PASSWORD) " +
				" VALUES(?,?) " +
				" SELECT @@identity FROM USR_IDENT";
		stmt = connection.prepareStatement(queryStr);
		stmt.setString(1,username);
		stmt.setString(2,passwordMD5);
		
		int userId;
		
			try
			{
				rs = stmt.executeQuery();
				rs.next();
				userId = rs.getInt(1);
	
			}
			catch(Exception ex)
			{
				return false;
			}
			
			
			queryStr = "INSERT INTO USR_GRP_RELATIONS " +
					" (USR_ID,USR_GRP_ID) " +
					" VALUES(?,?) ";
			stmt = connection.prepareStatement(queryStr);
			stmt.setInt(1,userId);
			stmt.setInt(2,1);
			
				try
				{
					stmt.executeUpdate();
	
				}
				catch(Exception ex)
				{
					return false;
				}
			
			return true;
	}


	public void addUserRight(int userID, int rightID) throws Exception
	{
		PreparedStatement stmt;
		String queryStr = "INSERT INTO USR_RIGHT_OWNER " +
				" (RIGHT_USR_ID,RIGHT_ID) " +
				" VALUES(?,?) ";
		stmt = connection.prepareStatement(queryStr);
		stmt.setInt(1,userID);
		stmt.setInt(2,rightID);
	
				stmt.executeUpdate();
	}


	public boolean addUserToGrpRelations(int userId,int groupID) throws Exception 
	{
	
		PreparedStatement stmt;
		String queryStr = "INSERT INTO USR_GRP_RELATIONS " +
				"(USR_ID, USR_GRP_ID) " +
				"VALUES (?,?)";
			stmt = connection.prepareStatement(queryStr);
			stmt.setInt(1,userId);
			stmt.setInt(2,groupID);
			
			try {
				stmt.executeUpdate();
			} catch(Exception ex) 
			{
				return false;
			}
		
		return true;
	}


	public boolean addUserToGrpRelations(String username,int groupID) throws Exception 
	{
		ResultSet rs;
		PreparedStatement stmt;
		String queryStr = "SELECT USR_ID FROM USR_IDENT " +
				"WHERE USR_USERNAME = ? ";
		stmt = connection.prepareStatement(queryStr);
		stmt.setString(1,username);
		rs = stmt.executeQuery();
		int userID = rs.getInt(1);
		
		
		
		queryStr = "INSERT INTO USR_GRP_RELATIONS " +
				"(USR_ID, USR_GRP_ID) " +
				"VALUES (?,?)";
			stmt = connection.prepareStatement(queryStr);
			stmt.setInt(1,userID);
			stmt.setInt(1,groupID);
			
			try {
				stmt.executeUpdate();
			} catch(Exception ex) 
			{
				return false;
			}
		
		return true;
	}


	public boolean deleteAction(int actionID) throws Exception
	{
	
		deleteActionHistory(actionID);
		PreparedStatement stmt;
		String queryStr = "DELETE FROM USR_ACTION WHERE (ACT_ID = ?)";
		stmt = connection.prepareStatement(queryStr);
		stmt.setLong(1,actionID);
	
			try
			{
				stmt.executeUpdate();
			}
			catch(Exception ex)
			{
				return false;
			}
			return true;
	}


	public boolean deleteActionHistory(int actionID) throws Exception
	{
		
		PreparedStatement stmt;
		String queryStr = "DELETE FROM USR_ACTION_HISTORY WHERE (ACT_ID = ?)";
		stmt = connection.prepareStatement(queryStr);
		stmt.setLong(1,actionID);
	
			try
			{
				stmt.executeUpdate();
			}
			catch(Exception ex)
			{
				return false;
			}
			return true;
	}


	public void deleteAllGroupAuthorizations(int groupId) throws Exception
		{
			PreparedStatement stmt;
			String queryStr = "DELETE FROM GRP_RIGHT_OWNER " +
					"WHERE RIGHT_GROUP_ID = ?";
			stmt = connection.prepareStatement(queryStr);
			stmt.setInt(1, groupId);
			stmt.executeUpdate();
	
	}


	public boolean deleteAuth(int authID) throws Exception
	{
	
		deleteUserRights(authID);
		
		PreparedStatement stmt;
		String queryStr = "DELETE FROM USR_RIGHT WHERE (RIGHT_ID = ?)";
		stmt = connection.prepareStatement(queryStr);
		stmt.setLong(1,authID);
	
			try
			{
				stmt.executeUpdate();
			}
			catch(Exception ex)
			{
				return false;
			}
			return true;
	}


	public boolean deleteFolder(int folderID) throws Exception
	{
		
		PreparedStatement stmt;
		String queryStr = "DELETE FROM FLD_IDENT WHERE (FLD_ID = ?)";
		stmt = connection.prepareStatement(queryStr);
		stmt.setLong(1,folderID);
	
			try
			{
				stmt.executeUpdate();
			}
			catch(Exception ex)
			{
				return false;
			}
			return true;
	}


	public boolean deleteForm(int formId) throws Exception
	{
	
		deleteFormObjects(formId);
		PreparedStatement stmt;
		String queryStr = "DELETE FROM FRM_IDENT WHERE (FRM_ID = ?)";
		stmt = connection.prepareStatement(queryStr);
		stmt.setLong(1,formId);
	
			try
			{
				stmt.executeUpdate();
			}
			catch(Exception ex)
			{
				return false;
			}
			return true;
	}


	public boolean deleteFormObjects(int formId) throws Exception
	{
	
		
		PreparedStatement stmt;
		String queryStr = "DELETE FROM FRM_OBJECT WHERE (FRM_ID = ?)";
		stmt = connection.prepareStatement(queryStr);
		stmt.setLong(1,formId);
	
			try
			{
				stmt.executeUpdate();
			}
			catch(Exception ex)
			{
				return false;
			}
			return true;
	}


	public boolean deleteGroup(int groupID) throws Exception
	{
		
		PreparedStatement stmt;
		String queryStr;
		
		queryStr ="DELETE FROM GRP_RELATIONS WHERE GRP_ID = ? OR GRP_CHILD_ID = ?";
		stmt = connection.prepareStatement(queryStr);
		stmt.setLong(1,groupID);
		stmt.setLong(2,groupID);
		stmt.executeUpdate();
		
		queryStr ="DELETE FROM USR_GRP_RELATIONS WHERE USR_GRP_ID = ? ";
		stmt = connection.prepareStatement(queryStr);
		stmt.setLong(1,groupID);
		stmt.executeUpdate();
		
		queryStr = "DELETE FROM GRP_IDENT WHERE (GRP_ID = ?)";
		stmt = connection.prepareStatement(queryStr);
		stmt.setLong(1,groupID);
	
			try
			{
				stmt.executeUpdate();
			}
			catch(Exception ex)
			{
				return false;
			}
			return true;
	}


	public boolean deleteGroupById(String groupID) throws Exception
	{
		PreparedStatement stmt;
		String queryStr = "DELETE FROM GRP_IDENT " +
				" WHERE GRP_ID = ? ";
		stmt = connection.prepareStatement(queryStr);
	
		stmt.setInt(1,Integer.valueOf(groupID).intValue());
	
			try
			{
				stmt.executeUpdate();
			}
			catch(Exception ex)
			{
				System.out.println(ex.getMessage());
				return false;
			}
			return true;
	}


	public boolean deleteProperty(String prefix, int propid) throws Exception
	{
	
		PreparedStatement stmt;
		String queryStr = "DELETE FROM " + prefix + "_PROP_IDENT " +
				"WHERE (PROP_ID = ?)";
		stmt = connection.prepareStatement(queryStr);
		stmt.setInt(1,propid);
	
			try
			{
				stmt.executeUpdate();
			}
			catch(Exception ex)
			{
				return false;
			}
			return true;
	}


	public boolean deletePropertyValues(String prefix, int propid) throws Exception
	{
	
		
		PreparedStatement stmt;
		String queryStr = "DELETE FROM " + prefix + "_PROP " +
				"WHERE (PROP_ID = ?)";
		stmt = connection.prepareStatement(queryStr);
		stmt.setInt(1,propid);
	
			try
			{
				stmt.executeUpdate();
			}
			catch(Exception ex)
			{
				return false;
			}
			return true;
	}



	public boolean deleteUser(long userID) throws Exception
	{
		deleteUserActions(Integer.parseInt(String.valueOf(userID)));
		deleteUserRights(Integer.parseInt(String.valueOf(userID)));
		deleteUserFromGrpRelations((int) userID);
		deleteUserProps(String.valueOf(userID));
		
		PreparedStatement stmt;
		String queryStr = "DELETE FROM USR_IDENT WHERE (USR_ID = ?)";
		stmt = connection.prepareStatement(queryStr);
		stmt.setLong(1,userID);
	
			try
			{
				stmt.executeUpdate();
			}
			catch(Exception ex)
			{
				return false;
			}
			return true;
	}


	public void deleteUserActions(int userID) throws Exception
	{
		PreparedStatement stmt;
			String queryStr = "DELETE FROM USR_ACTION_HISTORY " +
					" WHERE USR_ID = ?";
			stmt = connection.prepareStatement(queryStr);
			stmt.setInt(1,userID);
			stmt.executeUpdate();
	}


	public void deleteUserFromGrpRelations(int userID) throws Exception
	{
		PreparedStatement stmt;
			String queryStr = "DELETE FROM USR_GRP_RELATIONS " +
					" WHERE USR_ID = ?";
			stmt = connection.prepareStatement(queryStr);
			stmt.setInt(1,userID);
			stmt.executeUpdate();
	}


	public void deleteUserProps(String userID) throws Exception
	{
		PreparedStatement stmt;
			String queryStr = "DELETE FROM USR_PROP " +
					" WHERE USR_ID = ?";
			stmt = connection.prepareStatement(queryStr);
			stmt.setString(1,userID);
			stmt.executeUpdate();
			
	}


	public void deleteUserRights(int userID) throws Exception
	{
		PreparedStatement stmt;
			String queryStr = "DELETE FROM USR_RIGHT_OWNER " +
					" WHERE RIGHT_USR_ID = ?";
			stmt = connection.prepareStatement(queryStr);
			stmt.setInt(1,userID);
			stmt.executeUpdate();
			
	}


	public ResultSet getActionsByID(String [] strArrIDs) throws Exception
	{
		String strIDs = "(";
		for (int i=0;i<strArrIDs.length;i++)
			strIDs += strArrIDs[i] + ",";
		
		strIDs = strIDs.substring(0,strIDs.length()-1);
		strIDs += " )";
	
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT * from USR_ACTION " +
					" WHERE USR_ACTION.ACT_ID IN " + strIDs + " ";
			stmt = connection.prepareStatement(queryStr);
			rs = stmt.executeQuery();
			return rs;
	}


	public ResultSet getActionSearchResults(String strSort, String strLike, String strSortBy, String strSearchBy) throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT * from USR_ACTION WHERE "+strSearchBy+" LIKE'%" + strLike + "%'" +
					" ORDER BY "+strSortBy+ " " + strSort ;
			stmt = connection.prepareStatement(queryStr);
			rs = stmt.executeQuery();
			return rs;
	}


	public ResultSet getAllActionIds() throws Exception
	{
	
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT ACT_ID from USR_ACTION " +
					" ORDER BY ACT_ID DESC";
			stmt = connection.prepareStatement(queryStr);
			rs = stmt.executeQuery();
			return rs;
	}


	public ResultSet getAllActionSet(String strSort, String strLike, String strSortBy, String strSearchBy) throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT * from USR_ACTION WHERE "+strSearchBy+" LIKE'%" + strLike + "%'" +
					" ORDER BY "+strSortBy+ " " + strSort ;
			stmt = connection.prepareStatement(queryStr);
			rs = stmt.executeQuery();
			return rs;
	}


	public ResultSet getAllAuthorizationIds() throws Exception
	{
	
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT RIGHT_ID from USR_RIGHT " +
					" ORDER BY RIGHT_ID DESC";
			stmt = connection.prepareStatement(queryStr);
			rs = stmt.executeQuery();
			return rs;
	}


	public ResultSet getAllAuthorizationSet(String strLike, String strSort,String strSortBy, String strSearchBy) throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT * from USR_RIGHT WHERE "+strSearchBy+" LIKE '%"+strLike+"%' " +
					"ORDER BY "+strSortBy+ " " +strSort;
			stmt = connection.prepareStatement(queryStr);
			rs = stmt.executeQuery();
			return rs;
	}


	public ResultSet getAllFolderIds() throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT FLD_ID from FLD_IDENT ORDER BY FLD_NAME DESC ";
			stmt = connection.prepareStatement(queryStr);
			rs = stmt.executeQuery();
			return rs;
	}


	public ResultSet getAllFolderSet(String strSort) throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT * from FLD_IDENT ORDER BY FLD_NAME " + strSort;
			stmt = connection.prepareStatement(queryStr);
			rs = stmt.executeQuery();
			return rs;
	}


	public ResultSet getAllFormSet(String strLike,String strSort,String strSortBy, String strSearchBy) throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT * from FRM_IDENT WHERE "+strSearchBy+" LIKE '%" + strLike + "%'" +
			" ORDER BY "+strSortBy+ " " + strSort ;
			stmt = connection.prepareStatement(queryStr);
			rs = stmt.executeQuery();
			return rs;
	}


	public ResultSet getAllGroupSet() throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT * " +
					"from GRP_IDENT " +
					" ORDER BY GRP_ID DESC";
			stmt = connection.prepareStatement(queryStr);
			rs = stmt.executeQuery();
			return rs;
	}


	public ResultSet getAllProperties(String prefix) throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT * " +
					"FROM " + prefix + "_PROP_IDENT ORDER BY PROP_NAME ASC";
			stmt = connection.prepareStatement(queryStr);
			rs = stmt.executeQuery();
			return rs;
	}


	public ResultSet getAllUserSet() throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT * from USR_IDENT" +
					" ORDER BY USR_ID DESC";
			
			stmt = connection.prepareStatement(queryStr);
			rs = stmt.executeQuery();
			return rs;
	}


	public ResultSet getAuthsByID(String [] strArrIDs) throws Exception
	{
		String strIDs = "(";
		for (int i=0;i<strArrIDs.length;i++)
			strIDs += strArrIDs[i] + ",";
		
		strIDs = strIDs.substring(0,strIDs.length()-1);
		strIDs += " )";

		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT * from USR_RIGHT " +
					" WHERE USR_RIGHT.RIGHT_ID IN " + strIDs + "";

			stmt = connection.prepareStatement(queryStr);
			rs = stmt.executeQuery();
			return rs;
	}
	



	public ResultSet getAuthSearchResults(String strSort, String strLike, String strSortBy, String strSearchBy) throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT * from USR_RIGHT WHERE "+strSearchBy+" LIKE'%" + strLike + "%'" +
					" ORDER BY "+strSortBy+ " " + strSort ;
			stmt = connection.prepareStatement(queryStr);
			rs = stmt.executeQuery();
			return rs;
	}


	public ResultSet getChildFolders(int fldID) throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT * " +
					"FROM FLD_IDENT WHERE FLD_PARENT_ID = ?";
			stmt = connection.prepareStatement(queryStr);
			stmt.setInt(1, fldID);
			rs = stmt.executeQuery();
			return rs;
	}


	public ResultSet getFolderById(String folderID) throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT * from FLD_IDENT WHERE FLD_ID = " + folderID;
			stmt = connection.prepareStatement(queryStr);
			rs = stmt.executeQuery();
			return rs;
	}


	public ResultSet getFormsByID(String [] strArrIDs) throws Exception
	{
		String strIDs = "(";
		for (int i=0;i<strArrIDs.length;i++)
			strIDs += strArrIDs[i] + ",";
		
		strIDs = strIDs.substring(0,strIDs.length()-1);
		strIDs += " )";
	
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT * from FRM_IDENT " +
					" WHERE FRM_IDENT.FRM_ID IN " + strIDs + "";
	
			stmt = connection.prepareStatement(queryStr);
			rs = stmt.executeQuery();
			return rs;
	}


	public ResultSet getGroupAuthorizations(int groupId) throws Exception 
	{
	
	
			ResultSet rs;
			PreparedStatement stmt;
				String queryStr = "SELECT USR_RIGHT.RIGHT_ID, RIGHT_CODE, RIGHT_DESC " +
						"FROM USR_RIGHT AS USR_RIGHT, GRP_RIGHT_OWNER AS GRP_RIGHT_OWNER " +
						"WHERE USR_RIGHT.RIGHT_ID = GRP_RIGHT_OWNER.RIGHT_ID " +
						"AND GRP_RIGHT_OWNER.RIGHT_GROUP_ID = ?";
				stmt = connection.prepareStatement(queryStr);
				stmt.setInt(1, groupId);
				rs = stmt.executeQuery();
				return rs;
	}


	public ResultSet getGroupById(String groupID) throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT * from GRP_IDENT WHERE GRP_ID = " + groupID;
			stmt = connection.prepareStatement(queryStr);
			rs = stmt.executeQuery();
			return rs;
	}


	public ResultSet getGroupsByID(String [] strArrIDs) throws Exception
	{
		String strIDs = "(";
		for (int i=0;i<strArrIDs.length;i++)
			strIDs += strArrIDs[i] + ",";
		
		strIDs = strIDs.substring(0,strIDs.length()-1);
		strIDs += " )";
	
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT * from GRP_IDENT " +
					" WHERE GRP_IDENT.GRP_ID IN " + strIDs + "  ";
			stmt = connection.prepareStatement(queryStr);
			rs = stmt.executeQuery();
			return rs;
	}


	public ResultSet getGroupSearchResults(String strSort, String strLike, String strSortBy, String strSearchBy) throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT * from GRP_IDENT WHERE "+strSearchBy+" LIKE'%" + strLike + "%'" +
					" ORDER BY "+strSortBy+ " " + strSort ;
			stmt = connection.prepareStatement(queryStr);
			rs = stmt.executeQuery();
			return rs;
	}


	public ResultSet getGroupUsers(int groupId) throws Exception 
	{
	
	
			ResultSet rs;
			PreparedStatement stmt;
				String queryStr = "SELECT USR_ID " +
						"from USR_GRP_RELATIONS " +
						" WHERE USR_GRP_ID = ?";
				stmt = connection.prepareStatement(queryStr);
				stmt.setInt(1, groupId);
				rs = stmt.executeQuery();
				return rs;
	}


	public ResultSet getObjPropsByID(String [] strArrIDs, String tblPrefix, String userId) throws Exception
	{
		String strIDs = "(";
		for (int i=0;i<strArrIDs.length;i++)
			strIDs += strArrIDs[i] + ",";
		
		strIDs = strIDs.substring(0,strIDs.length()-1);
		strIDs += " )";
	
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT PROP_NAME,PROP_VALUE,"+tblPrefix+"_PROP_IDENT.PROP_ID " +
					"from "+tblPrefix+"_PROP AS "+tblPrefix+"_PROP , "+tblPrefix+"_PROP_IDENT AS "+tblPrefix+"_PROP_IDENT " +
					" WHERE ( "+tblPrefix+"_PROP_IDENT.PROP_ID IN " + strIDs + " ) " +
							"AND USR_PROP.PROP_ID = USR_PROP_IDENT.PROP_ID " +
							"AND USR_PROP.USR_ID = ?";
	
			stmt = connection.prepareStatement(queryStr);
			stmt.setInt(1,Integer.parseInt(userId));
			rs = stmt.executeQuery(); 
			return rs;
	}


	public ResultSet getParentGroups(int groupID, String strSort) throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT * " +
					" FROM GRP_IDENT" +
					" WHERE GRP_ID IN (SELECT GRP_ID FROM GRP_RELATIONS WHERE GRP_CHILD_ID = ?)  " +
					" ORDER BY GRP_IDENT.GRP_NAME " + strSort;
			
			stmt = connection.prepareStatement(queryStr);
	
			stmt.setInt(1,groupID);
	
			rs = stmt.executeQuery();
			return rs;
	}


	public ResultSet getPropertiesById(String prefix) throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT " + prefix + "_PROP_IDENT.* , " + prefix + "_PROP." + prefix + "_ID " +
					"FROM " + prefix + "_PROP_IDENT AS " + prefix + "_PROP_IDENT, " + prefix + "_PROP AS " + prefix + "_PROP " +
							" WHERE " + prefix + "_PROP_IDENT.PROP_ID = " + prefix + "_PROP.PROP_ID " +
							" ORDER BY PROP_NAME ASC";
			stmt = connection.prepareStatement(queryStr);
			rs = stmt.executeQuery();
			return rs;
	}


	public ResultSet getPropsByGroupId(int groupId) throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT PROP_NAME,PROP_VALUE,GRP_PROP_IDENT.PROP_ID " +
					"FROM GRP_PROP AS GRP_PROP ,GRP_PROP_IDENT AS GRP_PROP_IDENT " +
					"WHERE GRP_PROP.PROP_ID = GRP_PROP_IDENT.PROP_ID " +
					"AND " +
					"GRP_PROP.GRP_ID = ?";
			stmt = connection.prepareStatement(queryStr);
			stmt.setInt(1,groupId);
			rs = stmt.executeQuery();
			return rs;
	}


	public ResultSet getPropsByID(String [] strArrIDs, String tblPrefix) throws Exception
	{
		String strIDs = "(";
		for (int i=0;i<strArrIDs.length;i++)
			strIDs += strArrIDs[i] + ",";
		
		strIDs = strIDs.substring(0,strIDs.length()-1);
		strIDs += " )";
	
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT * from "+tblPrefix+"_PROP_IDENT " +
					" WHERE ( "+tblPrefix+"_PROP_IDENT.PROP_ID IN " + strIDs + " ) ";
			
			stmt = connection.prepareStatement(queryStr);
			rs = stmt.executeQuery();
			return rs;
	}


	public ResultSet getPropsByUserId(int userId) throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT PROP_NAME,PROP_VALUE,USR_PROP_IDENT.PROP_ID " +
					"FROM USR_PROP AS USR_PROP, USR_PROP_IDENT AS USR_PROP_IDENT" +
					"WHERE USR_PROP.PROP_ID = USR_PROP_IDENT.PROP_ID " +
					"AND " +
					"USR_PROP.USR_ID = ?";
			stmt = connection.prepareStatement(queryStr);
			stmt.setInt(1,userId);
			rs = stmt.executeQuery();
			return rs;
	}


	public ResultSet getSubGroups(int groupID, String strSort) throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT * " +
					" FROM GRP_IDENT" +
					" WHERE GRP_ID IN (SELECT GRP_CHILD_ID FROM GRP_RELATIONS WHERE GRP_ID = ?)  " +
					" ORDER BY GRP_IDENT.GRP_NAME " + strSort;
			
			stmt = connection.prepareStatement(queryStr);
	
			stmt.setInt(1,groupID);
	
			rs = stmt.executeQuery();
			return rs;
	}


	public ResultSet getUserGroupSet(int userID) throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT USR_GRP_ID " +
					" FROM USR_GRP_RELATIONS " +
					" WHERE USR_ID = ? ";
			stmt = connection.prepareStatement(queryStr);
			stmt.setInt(1,userID);
			rs = stmt.executeQuery();
			return rs;
	}


	public ResultSet getUsersByGroup(int groupId) throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT * from USR_IDENT AS USR_IDENT, USR_GRP_RELATIONS AS USR_GRP_RELATIONS" +
			"WHERE USR_GRP_RELATIONS.USR_GRP_ID = ?" +
			"ORDER BY USR_ID DESC";
			
			stmt = connection.prepareStatement(queryStr);
			stmt.setInt(1,groupId);
			rs = stmt.executeQuery();
			return rs;
	}


	public ResultSet getUsersByID(String [] strArrIDs) throws Exception
	{
		String strIDs = "(";
		for (int i=0;i<strArrIDs.length;i++)
			strIDs += strArrIDs[i] + ",";
		
		strIDs = strIDs.substring(0,strIDs.length()-1);
		strIDs += " )";
	
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT * from USR_IDENT " +
					" WHERE ( USR_ID IN " + strIDs + " ) "
					+ " ORDER BY USR_IDENT.USR_USERNAME DESC";
			stmt = connection.prepareStatement(queryStr);
			rs = stmt.executeQuery();
			return rs;
	}


	public ResultSet getUserSearchResults(String strSort, String strLike, String strSortBy, String strSearchBy) throws Exception
	{
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT * from USR_IDENT WHERE "+strSearchBy+" LIKE '%" + strLike + "%'" +
					" ORDER BY "+strSortBy+ " " + strSort ;
			stmt = connection.prepareStatement(queryStr);
			rs = stmt.executeQuery();
			return rs;
	}

	/** returns the user set having all of 
	 * the property name-value pairs in the input Properties object
	 * Used in Jimet searching function.
	 * @param pprop
	 * @return resultset containing the search results
	 * @throws Exception
	 */
	public ResultSet getUserSearchResultsByProps(Properties pprop) throws Exception
	{
		
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT * " +
					"FROM USR_IDENT WHERE ";
			
			Enumeration en;
			for(int i=0; i< pprop.size(); i++)
			{
				queryStr += " EXISTS " +
						"( " +
						"SELECT * " +
						"FROM USR_PROP UP, USR_PROP_IDENT UPI " +
						"WHERE UP.PROP_ID = UPI.PROP_ID  " +
						"AND USR_IDENT.USR_ID = UP.USR_ID " +
						"AND UPI.PROP_NAME = ?  " +
						"AND UP.PROP_VALUE LIKE ? ) AND";
			}
			
			queryStr = queryStr.substring(0,queryStr.length()-5);
			queryStr += ")";
			
			stmt = connection.prepareStatement(queryStr);
			
			en = pprop.propertyNames();
			int i = 1;
			while(en.hasMoreElements())
			{
				String pname = (String) en.nextElement();
				String pvalue = pprop.getProperty(pname);
				System.out.println(pname + ":" + pvalue);
				stmt.setString(i++,pname);
				stmt.setString(i++,"%"+pvalue+"%");
			}			

			System.out.println(queryStr);
			rs = stmt.executeQuery();
			return rs;
	}


	public void removeAllUsersFromGroup(int groupId) throws Exception
	{
		PreparedStatement stmt;
			String queryStr = "DELETE FROM USR_GRP_RELATIONS " +
							"WHERE USR_GRP_ID = ?";
			stmt = connection.prepareStatement(queryStr);
			stmt.setInt(1,groupId);
			stmt.executeUpdate();
	}


	public boolean removeProperty(String prefix, int propId, int objId) throws Exception
	{
	
		PreparedStatement stmt;
		String queryStr = "DELETE FROM " + prefix + "_PROP " +
				"WHERE (PROP_ID = ?) " +
				"AND " + prefix + "_ID = ?";
		stmt = connection.prepareStatement(queryStr);
		stmt.setInt(1,propId);
		stmt.setInt(2,objId);
			try
			{
				stmt.executeUpdate();
			}
			catch(Exception ex)
			{
				return false;
			}
			return true;
	}


	public void removeUserFromGrpRelations(int userId,int groupId) throws Exception
	{
		PreparedStatement stmt;
			String queryStr = "DELETE FROM USR_GRP_RELATIONS " +
					" WHERE USR_ID = ?" +
					"AND USR_GRP_ID = ?";
			stmt = connection.prepareStatement(queryStr);
			stmt.setInt(1,userId);
			stmt.setInt(2,groupId);
			stmt.executeUpdate();
	}


	public void setParentGroupsById(int groupID, String[] allParents) throws Exception
	{
		Statement stmt;
		String queryStr = "DELETE FROM GRP_RELATIONS " +
				"WHERE GRP_CHILD_ID =  " + groupID;
		stmt = connection.createStatement();
		stmt.execute(queryStr);
	
	if(allParents !=null)
	{
	
		for(int k=0;k<allParents.length;k++)
		{
			queryStr = "INSERT INTO GRP_RELATIONS (GRP_ID, GRP_CHILD_ID)" +
				"VALUES ( " + allParents[k] + " , " + groupID + " ) ";
		stmt.execute(queryStr);
	
		}
	}
		
	}


	public void setSubGroupsById(int groupID, String[] allMembers) throws Exception
	{
		Statement stmt;
		String queryStr = "DELETE FROM GRP_RELATIONS " +
				"WHERE GRP_ID =  " + groupID;
		stmt = connection.createStatement();
		stmt.execute(queryStr);
	
	if(allMembers !=null)
	{
		for(int k=0;k<allMembers.length;k++)
		{
			queryStr = "INSERT INTO GRP_RELATIONS (GRP_ID, GRP_CHILD_ID)" +
				"VALUES ( " + groupID + " , " +  allMembers[k] + " ) ";
		stmt.execute(queryStr);
	
		}
	}
		
	}


	public boolean updateFolder(int folderID, String foldername, String description, int parentID) throws Exception
	{
		PreparedStatement stmt;
		String queryStr = "UPDATE FLD_IDENT " +
				"SET FLD_NAME = ? , FLD_DESC = ?, FLD_PARENT_ID = ? " +
				" WHERE FLD_ID = " + folderID;
		stmt = connection.prepareStatement(queryStr);
		stmt.setString(1,foldername);
		stmt.setString(2,description);
		stmt.setInt(3,parentID);
	
			try
			{
				stmt.executeUpdate();
			}
			catch(Exception ex)
			{
				return false;
			}
			return true;
	}


	public int updateGroup(int groupID, String groupname, String description) throws Exception
	{
		PreparedStatement stmt;
		int id = -1;
		ResultSet rs;
		String queryStr = "UPDATE GRP_IDENT " +
				"SET GRP_NAME = ? , GRP_DESC = ? " +
				" WHERE GRP_ID = " + groupID;
		stmt = connection.prepareStatement(queryStr);
		stmt.setString(1,groupname);
		stmt.setString(2,description);
	
			try
			{
				stmt.executeUpdate();
				rs = connection.prepareStatement("SELECT SYSIBM.IDENTITY_VAL_LOCAL() FROM GRP_IDENT").executeQuery();
				rs.next();
				id = rs.getInt(1);				
			}
			catch(Exception ex)
			{
				System.out.println(ex.getMessage());
				return id;
			}
			return id;
	}


	public boolean updateProperty(String prefix, int propid, String name, String type) throws Exception
	{
		PreparedStatement stmt;
		String queryStr = "UPDATE " + prefix + "_PROP_IDENT " +
				"SET PROP_NAME = ? , PROP_TYPE = ? " +
				" WHERE PROP_ID = ? ";
		stmt = connection.prepareStatement(queryStr);
		stmt.setString(1,name);
		stmt.setString(2,type);
		stmt.setInt(3,propid);
			try
			{
				stmt.executeUpdate();
			}
			catch(Exception ex)
			{
				System.out.println(ex.getMessage());
				return false;
			}
			return true;
	}


	public boolean updatePropertyToObj(String prefix, int objId, int propId, String value) throws Exception
	{
		PreparedStatement stmt;
		String queryStr = "UPDATE "+prefix+"_PROP " +
				"SET PROP_VALUE = ? " +
				" WHERE PROP_ID = ? " +
				"AND "+prefix+"_ID = ?";
		stmt = connection.prepareStatement(queryStr);
		stmt.setString(1,value);
		stmt.setInt(2,propId);
		stmt.setInt(3,objId);
			try
			{
				stmt.executeUpdate();
			}
			catch(Exception ex)
			{
				System.out.println(ex.getMessage());
				return false;
			}
			return true;
	}


	public boolean updateUser(long userID, String username, String passwordMD5) throws Exception
	{
		
		PreparedStatement stmt;
		String queryStr = "UPDATE USR_IDENT " +
				"SET USR_USERNAME = ? , USR_PASSWORD = ?  " +
				" WHERE USR_ID = " + userID;
		stmt = connection.prepareStatement(queryStr);
		stmt.setString(1,username);
		stmt.setString(2,passwordMD5);
	
	
			try
			{
				stmt.executeUpdate();
			}
			catch(Exception ex)
			{
				System.out.println(ex.getMessage());
				return false;
			}
			return true;
	}
	
	
}
